#!/bin/bash

APP=edu
# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else 
    do_date=`date -d "-1 day" +%F`
fi

dwd_user_login_inc="
insert overwrite table ${APP}.dwd_user_login_inc partition(dt='$do_date')
select
    user_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') login_time,
    channel,
    province_id,
    version_code,
    mid_id,
    brand,
    model,
    operate_system
from
(
    select
        user_id,
        channel,
        area_code,
        version_code,
        mid_id,
        brand,
        model,
        operate_system,
        ts
    from
    (
        select
            user_id,
            channel,
            area_code,
            version_code,
            mid_id,
            brand,
            model,
            operate_system,
            ts,
            row_number() over (partition by session_id order by ts) rn
        from
        (
            select
                user_id,
                channel,
                area_code,
                version_code,
                mid_id,
                brand,
                model,
                operate_system,
                ts,
                concat(mid_id,'-',last_value(session_start_point,true) over(partition by mid_id order by ts)) session_id
            from
            (
                select
                    common.uid user_id,
                    common.ch channel,
                    common.ar area_code,
                    common.vc version_code,
                    common.mid mid_id,
                    common.ba brand,
                    common.md model,
                    common.os operate_system,
                    ts,
                    if(page.last_page_id is null,ts,null) session_start_point
                from ${APP}.ods_log_inc
                where dt='$do_date'
                and page is not null
            )t1
        )t2
        where user_id is not null
    )t3
    where rn=1
)t4
left join
(
    select
        id province_id,
        area_code
    from ${APP}.ods_base_province_full
    where dt='$do_date'
)bp
on t4.area_code=bp.area_code;
"

dwd_traffic_page_view_inc="
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_page_view_inc partition (dt='$do_date')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    user_id,
    version_code,
    page_item,
    page_item_type,
    last_page_id,
    page_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') view_time,
    concat(mid_id,'-',last_value(session_start_point,true) over (partition by mid_id order by ts)) session_id,
    during_time
from
(
    select
        common.ar area_code,
        common.ba brand,
        common.ch channel,
        common.is_new is_new,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code,
        page.during_time,
        page.item page_item,
        page.item_type page_item_type,
        page.last_page_id,
        page.page_id,
        ts,
        if(page.last_page_id is null,ts,null) session_start_point
    from ${APP}.ods_log_inc
    where dt='$do_date'
    and page is not null
)log
left join
(
    select
        id province_id,
        area_code
    from ${APP}.ods_base_province_full
    where dt='$do_date'
)bp
on log.area_code=bp.area_code;
"

dwd_traffic_action_inc="set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_action_inc partition(dt='$do_date')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    user_id,
    version_code,
    during_time,
    page_item,
    page_item_type,
    last_page_id,
    page_id,
    action_id,
    action_item,
    action_item_type,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') action_time
from
(
    select
        common.ar area_code,
        common.ba brand,
        common.ch channel,
        common.is_new,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code,
        page.during_time,
        page.item page_item,
        page.item_type page_item_type,
        page.last_page_id,
        page.page_id,
        action.action_id,
        action.item action_item,
        action.item_type action_item_type,
        action.ts
    from ${APP}.ods_log_inc lateral view explode(actions) tmp as action
    where dt='$do_date'
    and actions is not null
)log
left join
(
    select
        id province_id,
        area_code
    from ${APP}.ods_base_province_full
    where dt='$do_date'
)bp
on log.area_code=bp.area_code;"

dwd_traffic_display_inc="set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_display_inc partition(dt='$do_date')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    user_id,
    version_code,
    during_time,
    page_item,
    page_item_type,
    last_page_id,
    page_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') display_time,
    display_type,
    display_item,
    display_item_type,
    display_order,
    display_pos_id
from
(
    select
        common.ar area_code,
        common.ba brand,
        common.ch channel,
        common.is_new,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code,
        page.during_time,
        page.item page_item,
        page.item_type page_item_type,
        page.last_page_id,
        page.page_id,
        display.display_type,
        display.item display_item,
        display.item_type display_item_type,
        display.\`order\` display_order,
        display.pos_id display_pos_id,
        ts
    from ${APP}.ods_log_inc lateral view explode(displays) tmp as display
    where dt='$do_date'
    and displays is not null
)log
left join
(
    select
        id province_id,
        area_code
    from ${APP}.ods_base_province_full
    where dt='$do_date'
)bp
on log.area_code=bp.area_code;"

dwd_traffic_start_inc="set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_start_inc partition(dt='$do_date')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    user_id,
    version_code,
    entry,
    open_ad_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') action_time,
    loading_time,
    open_ad_ms,
    open_ad_skip_ms
from
(
    select
        common.ar area_code,
        common.ba brand,
        common.ch channel,
        common.is_new,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code,
         \`start\`.entry,
        \`start\`.first_open,
        \`start\`.loading_time,
        \`start\`.open_ad_id,
        \`start\`.open_ad_ms,
        \`start\`.open_ad_skip_ms,
        ts
    from ${APP}.ods_log_inc
    where dt='$do_date'
    and \`start\` is not null
)log
left join
(
    select
        id province_id,
        area_code
    from ${APP}.ods_base_province_full
    where dt='$do_date'
)bp
on log.area_code=bp.area_code;"

dwd_traffic_error_inc="set hive.cbo.enable=false;
set hive.execution.engine=mr;
insert overwrite table ${APP}.dwd_traffic_error_inc partition(dt='$do_date')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    user_id,
    version_code,
    page_item,
    page_item_type,
    last_page_id,
    page_id,
    entry,
    loading_time,
    open_ad_id,
    open_ad_ms,
    open_ad_skip_ms,
    actions,
    displays,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') error_time,
    error_code,
    error_msg
from
(
    select
        common.ar area_code,
        common.ba brand,
        common.ch channel,
        common.is_new,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code,
        page.during_time,
        page.item page_item,
        page.item_type page_item_type,
        page.last_page_id,
        page.page_id,
        \`start\`.entry,
        \`start\`.loading_time,
        \`start\`.open_ad_id,
        \`start\`.open_ad_ms,
        \`start\`.open_ad_skip_ms,
        actions,
        displays,
        err.error_code,
        err.msg error_msg,
        ts
    from ${APP}.ods_log_inc
    where dt='$do_date'
    and err is not null
)log
join
(
    select
        id province_id,
        area_code
    from ${APP}.ods_base_province_full
    where dt='$do_date'
)bp
on log.area_code=bp.area_code;"

dwd_traffic_video_play_inc="set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_video_play_inc partition(dt='$do_date')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    user_id,
    version_code,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    play_sec,
    position_sec,
    video_id
from
(
    select
        common.ar area_code,
        common.ba brand,
        common.ch channel,
        common.is_new,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code,
        appVideo.play_sec,
        appVideo.position_sec,
        appVideo.video_id,
        ts
    from ${APP}.ods_log_inc
    where dt='$do_date'
)log
left join
(
    select
        id province_id,
        area_code
    from ${APP}.ods_base_province_full
    where dt='$do_date'
)bp
on log.area_code=bp.area_code;"
dwd_trade_cart_add_inc="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dwd_trade_cart_add_inc partition (dt = '$do_date')
    select
       data.id,
    data.user_id,
    data.course_id,
	data.course_name,
	data.cart_price,
	data.session_id,
    date_format(data.create_time,'yyyy-MM-dd') create_time,
    date_format(data.update_time,'yyyy-MM-dd') update_time,
	data.deleted,
	data.sold
    from ${APP}.ods_cart_info_inc
    where dt = '$do_date'
    and type = 'bootstrap\-\insert';
"
	
dwd_trade_order_detail_inc="
insert overwrite table ${APP}.dwd_trade_order_detail_inc partition (dt='$do_date')
select
    od.id,
    course_id,
    course_name,
    order_id,
    od.user_id,
    origin_amount,
    coupon_reduce,
    final_amount,
    od.session_id,
    order_status,
    out_trade_no,
    trade_body,
    province_id,
    date_format(create_time,'yyyy-MM-dd') create_time,
    date_format(expire_time,'yyyy-MM-dd') expire_time,
    date_format(update_time,'yyyy-MM-dd') update_time
from
(
    select
        data.id,
        data.course_id,
        data.course_name,
        data.order_id,
        data.user_id,
           data.session_id,
        data.origin_amount,
        data.coupon_reduce,
        data.final_amount
    from ${APP}.ods_order_detail_inc
    where dt = '$do_date'
    and type = 'bootstrap-insert'
) od
left join
(
    select
        data.id,
        data.user_id,
		data.order_status,
		data.out_trade_no,
		data.trade_body,
		data.session_id,
        data.province_id,
		date_format(data.create_time,'yyyy-MM-dd') create_time,
        date_format(data.expire_time,'yyyy-MM-dd') expire_time,
        date_format(data.update_time,'yyyy-MM-dd') update_time
    from ${APP}.ods_order_info_inc
    where dt = '$do_date'
    and type = 'bootstrap-insert'
) oi
on od.order_id = oi.id;"

dwd_trade_pay_detail_inc="
insert overwrite table ${APP}.dwd_trade_pay_detail_inc partition (dt='do_date')
select
       od.id,
       out_trade_no,
       od.order_id,
       alipay_trade_no,
       payment_type,
       date_format(create_time, 'yyyy-MM-dd') creat_time,
       date_format(callback_time, 'yyyy-MM-dd') callback_time,
       user_id,
       origin_amount,
       coupon_reduce,
       final_amount,
       province_id
from (
         select data.id,
                data.out_trade_no,
                data.order_id,
                data.alipay_trade_no,
                data.payment_type,
                data.create_time,
                data.callback_time
         from ${APP}.ods_payment_info_inc
         where dt = '$do_date'
           and type = 'bootstrap-insert'
     ) od
         left join
     (
         select data.id,
                data.user_id,
                data.origin_amount,
                data.coupon_reduce,
                data.final_amount,
                data.province_id
         from ${APP}.ods_order_info_inc
         where dt = '$do_date'
           and type = 'bootstrap-insert'
     ) oi
     on od.order_id = oi.id;"

dwd_trade_cart_full="
insert overwrite table ${APP}.dwd_trade_cart_full partition(dt='$do_date')
select
    id,
    user_id,
    couse_id,
    couse_name,
    cart_price,
    session_id,
    date_format(create_time,'yyyy-MM-dd') create_time,
    date_format(update_time,'yyyy-MM-dd') update_time
from ${APP}.ods_cart_info_full
where dt='$do_date';"

dwd_chapter_process_inc="
insert overwrite table ${APP}.dwd_chapter_process_inc partition(dt='$do_date')
select
    data.id,
    data.course_id,
    data.chapter_id,
    data.user_id,
    data.position_sec,
    data.create_time,
    data.update_time,
    data.deleted
from ${APP}.ods_user_chapter_process_inc
where dt='$do_date';
"

dwd_test_exam_inc="
insert overwrite table ${APP}.dwd_test_exam_inc partition (dt='$do_date')
select
    test_exam_question.id,
    test_exam_question.exam_id,
    test_exam_question.paper_id,
    test_exam_question.question_id,
    test_exam_question.user_id,
    test_exam_question.answer,
    test_exam_question.is_correct,
    test_exam_question.question_score,
    test_exam.exam_score,
    test_exam.duration_sec,
	date_format(test_exam.create_time,'yyyy-MM-dd') create_time,
    date_format(test_exam.submit_time,'yyyy-MM-dd') submit_time,
    date_format(test_exam.update_time,'yyyy-MM-dd') update_time,
    test_exam.deleted
from
(
    select
        data.id,
        data.exam_id,
        data.paper_id,
        data.question_id,
        data.user_id,
        data.answer,
        data.is_correct,
        data.score question_score
    from ${APP}.ods_test_exam_question_inc
    where dt = '$do_date'
    and type = 'bootstrap-insert'
) test_exam_question
left join
(
    select
		data.id,
		data.paper_id,
        data.score exam_score,
		data.duration_sec,
		date_format(data.create_time,'yyyy-MM-dd') create_time,
        date_format(data.submit_time,'yyyy-MM-dd') submit_time,
        date_format(data.update_time,'yyyy-MM-dd') update_time,
		data.deleted
    from ${APP}.ods_test_exam_inc
    where dt = '$do_date'
    and type = 'bootstrap-insert'
) test_exam
on test_exam_question.paper_id = test_exam.paper_id;"

dwd_interaction_favor_add_inc="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dwd_interaction_favor_add_inc partition(dt='do_date')
select
    data.id,
    data.user_id,
    data.course_id,
    date_format(data.create_time,'yyyy-MM-dd') date_id,
    data.create_time
from ${APP}.ods_favor_info_inc
where dt='$do_date'
and type = 'bootstrap-insert';"

dwd_interaction_comment_inc="
insert overwrite table ${APP}.dwd_interaction_comment_inc partition(dt='$do_date')
select

        ch.id,
       ch.user_id,
       ch.chapter_id,
       ch.course_id,
       ch.comment_txt,
       ch.create_time,
       co.reivew_txt,
       co.reivew_stars,
       co.create_time
from
(select
        data.id,
        data.user_id,
        data.chapter_id,
        data.course_id,
        data.comment_txt,
        data.create_time
 from ${APP}.ods_comment_info_inc
 where dt = '$do_date'
 and type = 'bootstrap-insert'
)ch
left join
(select
        data.course_id,
       data.reivew_txt,
       data.reivew_stars,
       data.create_time
from ${APP}.ods_review_info_inc
where dt = '$do_date'
and type = 'bootstrap-insert'
)co
on ch.course_id = co.course_id;"

case $1 in
    "dwd_user_login_inc" )
        hive -e "$dwd_user_login_inc"
    ;;
    "dwd_traffic_page_view_inc" )
        hive -e "$dwd_traffic_page_view_inc"
    ;;
    "dwd_traffic_action_inc" )
        hive -e "$dwd_traffic_action_inc"
    ;;
    "dwd_traffic_display_inc" )
        hive -e "$dwd_traffic_display_inc"
    ;;
    "dwd_traffic_start_inc" )
        hive -e "$dwd_traffic_start_inc"
    ;;
    "dwd_traffic_error_inc" )
        hive -e "$dwd_traffic_error_inc"
    ;;
     "dwd_traffic_video_play_inc" )
        hive -e "$dwd_traffic_video_play_inc"
    ;;
    "dwd_trade_cart_add_inc" )
        hive -e "$dwd_trade_cart_add_inc"
    ;;
    "dwd_trade_cart_full" )
        hive -e "$dwd_trade_cart_full"
    ;;
    "dwd_trade_order_detail_inc" )
        hive -e "$dwd_trade_order_detail_inc"
    ;;
	"dwd_trade_pay_detail_inc" )
        hive -e "$dwd_trade_pay_detail_inc"
    ;;
    "dwd_chapter_process_inc" )
        hive -e "$dwd_chapter_process_inc"
    ;; 
	"dwd_test_exam_inc" )
        hive -e "$dwd_test_exam_inc"
    ;;
    "dwd_interaction_favor_add_inc" )
        hive -e "$dwd_interaction_favor_add_inc"
    ;;
	"dwd_interaction_comment_inc" )
        hive -e "$dwd_interaction_comment_inc"
    ;;
    "all" )
        hive -e "$dwd_trade_cart_add_inc$dwd_user_login_inc$dwd_traffic_page_view_inc$dwd_traffic_action_inc$dwd_traffic_display_inc$dwd_traffic_start_inc$dwd_traffic_error_inc$dwd_traffic_video_play_inc$dwd_trade_cart_full$dwd_trade_order_detail_inc$dwd_trade_pay_detail_inc$dwd_chapter_process_inc$dwd_test_exam_inc$dwd_interaction_favor_add_inc$dwd_interaction_comment_inc"
esac

